// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;

namespace net.windward.utils.ado.ODBC
{
	/// <summary>
	/// An ODBC database.
	/// </summary>
    public class WrOdbcDatabase : IWrDatabase, IComparable
	{
		private readonly DbProviderFactory provider;
		private readonly string odbcProvider;
		private readonly string server;
		private readonly string database;
		/// <summary>The SQL syntax for the vendor connected to (if it can be determined).</summary>
		protected IWrSyntax syntax;

		private WrTable[] tables;
		private WrView[] views;
		private WrStoredProcedure[] procedures;

		private static readonly Hashtable types;
		static WrOdbcDatabase()
		{

			types = new Hashtable();
			types.Add("counter", DbType.Int32);
			types.Add("currency", DbType.Currency);
			types.Add("float", DbType.Single);
			types.Add("integer", DbType.Int32);
			types.Add("longbinary", DbType.Binary);
			types.Add("longchar", DbType.Binary);
			types.Add("money", DbType.Currency);
		}

		/// <summary>
		/// Create a SqlServer database object.
		/// </summary>
		/// <param name="provider">The provider for this database.</param>
		/// <param name="odbcProvider">The ODBC Provider used.</param>
		/// <param name="server">The name of the server the database is on.</param>
		/// <param name="database">The name of the database.</param>
		public WrOdbcDatabase(DbProviderFactory provider, string odbcProvider, string server, string database)
		{
			this.provider = provider;
			this.odbcProvider = odbcProvider;
			this.server = server;
			this.database = database;
			syntax = WrOdbcVendor.CalcSyntax(provider, odbcProvider);
		}

		/// <summary>
		/// The name of the database.
		/// </summary>
		public string Name
		{
			get { return database; }
		}

		/// <summary>
		/// The default schema for all objects in this database. Returns null if there is no default.
		/// </summary>
		public string DefaultSchema
		{
			get
			{
				return null;
			}
		}

		/// <summary>
		/// All tables in this database. Must call LoadMetadata first.
		/// </summary>
		public WrTable[] Tables
		{
			get { return tables; }
		}

		/// <summary>
		/// All views in this database. Must call LoadMetadata first.
		/// </summary>
		public WrView[] Views
		{
			get { return views; }
		}

		/// <summary>
		/// Return all stored procedures in this database. Must call LoadMetadata first.
		/// </summary>
		public WrStoredProcedure[] StoredProcedures
		{
			get { return procedures; }
		}

		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="credentials">The user credentials to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(WrCredentials credentials, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{
			LoadMetadata(GetCommandLine(credentials), owner, status);
		}

		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			if (status != null)
				status(WrDatabase.TYPE.START, database);
			
			tables = GetTables(connStr, owner, status);
			for (int index = 0; index < tables.Length; index++)
			{
				WrColumn[] columns = GetColumns(tables[index], connStr);
				tables[index] = new WrTable(tables[index].Schema, tables[index].Name, tables[index].Description, tables[index].IsSystem, columns);
			}

			views = GetViews(connStr, owner, status);
			for (int index = 0; index < views.Length; index++)
			{
				WrColumn[] columns = GetColumns(views[index], connStr);
				views[index] = new WrView(views[index].Schema, views[index].Name, views[index].Description, views[index].IsSystem, columns);
			}

			procedures = GetStoredProcedures(connStr, owner, status);
		}

		/// <summary>
		/// Return all tables in this database.
		/// </summary>
		/// <param name="connectionString">The connection string to access the database.</param>
		/// <param name="owner">Determines what tables are returned and if metadata is also retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		/// <returns>all tables in this database.</returns>
		private static WrTable[] GetTables(string connectionString, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			if (status != null)
				status(WrDatabase.TYPE.TABLES, null);

			ArrayList rtn = new ArrayList();
			using (OdbcConnection conn = new OdbcConnection(connectionString))
			{
				conn.Open();
				DataTable schemaTable = conn.GetSchema("tables");
				int ordType = schemaTable.Columns.IndexOf("TABLE_TYPE");
				int ordName = schemaTable.Columns.IndexOf("TABLE_NAME");
				int ordRemarks = schemaTable.Columns.IndexOf("REMARKS");

				foreach (DataRow rowOn in schemaTable.Rows)
				{
					string tableName = (string) rowOn[ordName];
					if (status != null)
						status(WrDatabase.TYPE.TABLE, tableName);
					
					string type = ((string)rowOn[ordType]).ToUpper();
					if (((type == "TABLE") && ((owner & WrVendor.ELEM_OWNER.USER) != 0)) ||
							((type == "SYSTEM TABLE") && ((owner & WrVendor.ELEM_OWNER.SYSTEM) != 0)))
					{
						string desc = ((owner & WrVendor.ELEM_OWNER.DESCRIPTIONS) == 0) || rowOn.IsNull(ordRemarks)
						              	? "" : (string) rowOn[ordRemarks];
						int pos = desc.IndexOf((char) 0);
						if (pos > 0)
							desc = desc.Substring(0, pos);
						rtn.Add(new WrTable(null, tableName, desc, false, null));
					}
				}
			}
			rtn.Sort();
			return (WrTable[])rtn.ToArray(typeof(WrTable));
		}

		/// <summary>
		/// Return all views in this database.
		/// </summary>
		/// <param name="connectionString">The connection string to access the database.</param>
		/// <param name="owner">Determines what views are returned and if metadata is also retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		/// <returns>all views in this database.</returns>
		private static WrView[] GetViews(string connectionString, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			if (status != null)
				status(WrDatabase.TYPE.VIEWS, null);

			ArrayList rtn = new ArrayList();
			using (OdbcConnection conn = new OdbcConnection(connectionString))
			{
				conn.Open();
				DataTable schemaTable = conn.GetSchema("views");
				int ordType = schemaTable.Columns.IndexOf("TABLE_TYPE");
				int ordSchema = schemaTable.Columns.IndexOf("TABLE_SCHEM");
				if (ordSchema == -1)
					ordSchema = schemaTable.Columns.IndexOf("TABLE_OWNER");
				int ordName = schemaTable.Columns.IndexOf("TABLE_NAME");
				int ordRemarks = schemaTable.Columns.IndexOf("REMARKS");

				foreach (DataRow rowOn in schemaTable.Rows)
				{
					string viewName = (string)rowOn[ordName];
					if (status != null)
						status(WrDatabase.TYPE.VIEW, viewName);
					
					string type = rowOn[ordType] as string;
					string schema = ordSchema == -1 ? null : rowOn[ordSchema] as string;
					if ((type != null) && (type.ToUpper() == "VIEW"))
					{
						bool isSys = (schema != null) && ((schema.ToUpper() == "INFORMATION_SCHEMA") || (schema.ToUpper() == "SYS"));
						if (((! isSys) && ((owner & WrVendor.ELEM_OWNER.USER) != 0)) || (isSys && ((owner & WrVendor.ELEM_OWNER.SYSTEM) != 0)))
						{
							string desc = ((owner & WrVendor.ELEM_OWNER.DESCRIPTIONS) == 0) || rowOn.IsNull(ordRemarks)
							              	? ""
							              	: (string) rowOn[ordRemarks];
							int pos = desc.IndexOf((char) 0);
							if (pos > 0)
								desc = desc.Substring(0, pos);
							rtn.Add(new WrView(null, viewName, desc, false, null));
						}
					}
				}
			}
			rtn.Sort();
			return (WrView[])rtn.ToArray(typeof(WrView));
		}

		/// <summary>
		/// Return all columns in a table or view.
		/// </summary>
		/// <param name="table">The name of the table or view.</param>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <returns>all columns in a table or view.</returns>
		private static WrColumn[] GetColumns(WrBaseTable table, string connStr)
		{

			ArrayList rtn = new ArrayList();
			using (OdbcConnection conn = new OdbcConnection(connStr))
			{
				conn.Open();
				string[] restrictions = new string [] { null, null, table.FullName };

//				DataTable schemaKeys = conn.GetSchema("foreignkeys", restrictions);

				DataTable schemaTable = conn.GetSchema("columns", restrictions);
				int ordRemarks = schemaTable.Columns.IndexOf("REMARKS");
				int ordName = schemaTable.Columns.IndexOf("COLUMN_NAME");
				int ordType = schemaTable.Columns.IndexOf("TYPE_NAME");

				foreach (DataRow rowOn in schemaTable.Rows)
				{
					string str = (string)rowOn[ordType];
					int pos = str.IndexOf(' ');
					if (pos > 0)
						str = str.Substring(0, pos);
					DbType typ = ConvertFromRawType(str);
					str = rowOn.IsNull(ordRemarks) ? "" : (string)rowOn[ordRemarks];
					pos = str.IndexOf((char) 0);
					if (pos > 0)
						str = str.Substring(0, pos);
					rtn.Add(new WrColumn((string)rowOn[ordName], str, typ));
				}
			}
			return (WrColumn[])rtn.ToArray(typeof(WrColumn));
		}

		internal static DbType ConvertFromRawType(string name)
		{

			string key = name.ToLower();
			if (types.Contains(key))
				return (DbType)types[key];

			try
			{
				OdbcType typ = (OdbcType)Enum.Parse(typeof(OdbcType), name, true);
				OdbcParameter parm = new OdbcParameter();
				parm.OdbcType = typ;
				return parm.DbType;
			}
			catch (Exception)
			{
				return DbType.Object;
			}
		}

		/// <summary>
		/// Return all stored procedures in this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what stored procedures are returned and if metadata is also retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		/// <returns>all stored procedures in this database.</returns>
		private static WrStoredProcedure[] GetStoredProcedures(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			if (status != null)
				status(WrDatabase.TYPE.PROCEDURES, null);
			
			try
			{
				ArrayList rtn = new ArrayList();
				using (OdbcConnection conn = new OdbcConnection(connStr))
				{
					conn.Open();
					DataTable schemaTable = conn.GetSchema("procedures");
					int ordSchema = schemaTable.Columns.IndexOf("PROCEDURE_SCHEM");
					int ordName = schemaTable.Columns.IndexOf("PROCEDURE_NAME");

					foreach (DataRow rowOn in schemaTable.Rows)
					{
						string name = rowOn[ordName] as string;
						if (name == null)
							continue;
						if (name.IndexOf(';') != -1)
							name = name.Substring(0, name.IndexOf(';'));

						if (status != null)
							status(WrDatabase.TYPE.PROCEDURE, name);

						string schema = rowOn[ordSchema] as string;
						bool isSys = ((schema != null) && ((schema.ToUpper() == "INFORMATION_SCHEMA") || (schema.ToUpper() == "SYS"))) ||
						             name.StartsWith("dt_");
						if (((isSys) || ((owner & WrVendor.ELEM_OWNER.USER) == 0)) &&
						    (!isSys || ((owner & WrVendor.ELEM_OWNER.SYSTEM) == 0))) 
							continue;

						// get the parameters
						DataTable parmsDataTable = conn.GetSchema("ProcedureParameters", new string[] { null, null, name });
						DataColumn parmNameDataColumn = parmsDataTable.Columns["COLUMN_NAME"];
						DataColumn parmDirectionColumn = parmsDataTable.Columns["COLUMN_TYPE"];
						DataColumn parmTypeDataColumn = parmsDataTable.Columns["TYPE_NAME"];
						DataColumn parmSizeColumn = parmsDataTable.Columns["COLUMN_SIZE"];
						List<WrProcParam> procParams = new List<WrProcParam>();
						foreach (DataRow parmRow in parmsDataTable.Rows)
						{
							ParameterDirection dir;
							switch(int.Parse(parmRow[parmDirectionColumn].ToString()))
							{
								case 1:
									dir = ParameterDirection.Input;
									break;
								case 2:
								case 3:
								case 4:
									dir = ParameterDirection.Output;
									break;
								case 5:
									dir = ParameterDirection.ReturnValue;
									break;
								default:
									dir = ParameterDirection.InputOutput;
									break;
							}
							int size = parmRow.IsNull(parmSizeColumn) ? 0 : int.Parse(parmRow[parmSizeColumn].ToString());
							procParams.Add(new WrProcParam(parmRow[parmNameDataColumn].ToString(), 
										ConvertFromRawType(parmRow[parmTypeDataColumn].ToString()), 
										size, false, dir));
						} 

						rtn.Add(new WrStoredProcedure(null, name, "", false, procParams.ToArray()));
					}
				}
				rtn.Sort();
				return (WrStoredProcedure[])rtn.ToArray(typeof(WrStoredProcedure));
			}
			catch (Exception)
			{
				// generally means that the DB doesn't do stored procedures
				return new WrStoredProcedure[0];
			}
		}

		private string GetCommandLine(WrCredentials credentials)
		{
			return WrOdbcVendor.ConnectionString(provider, odbcProvider, server, database, credentials, true);
		}

		/// <summary>
		/// Standard compare.
		/// </summary>
		/// <param name="obj">Object compared to.</param>
		/// <returns>-1, 0, or 1.</returns>
	    public int CompareTo(object obj)
	    {
            WrOdbcDatabase db = obj as WrOdbcDatabase;
            if (db == null)
                return 1;
            return database.CompareTo(db.database);
        }
	}
}
